﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Reflection;

namespace Client.SqlServer
{
    public class DbContextmysql
    {
        /// <summary>
        /// 
        /// </summary>
        private MySqlConnection connection_;
        private string SqlConfigstr_;
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string SqlConfigstr
        {
            get
            {
                if (string.IsNullOrEmpty(SqlConfigstr_))
                {
                    //string file = System.Windows.Forms.Application.ExecutablePath;
                    //System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(file);
                    SqlConfigstr_ =
                         ConfigurationManager.AppSettings["Mysql"];
                    return SqlConfigstr_;
                }
                else
                {

                    return SqlConfigstr_;
                }
            }
            set
            {
                SqlConfigstr_ = value;
            }
        }

        public MySqlConnection connection
        {
            get
            {
                if(connection_ == null)
                {
                    var connection_ = new MySqlConnection(SqlConfigstr);
                    return connection_;
                }
                else
                {
                    return connection_;
                }
                

            }
        }

        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable Where(string sql)
        {
            try
            {
                using (MySqlCommand command = new MySqlCommand($"SELECT * FROM `outer_supplier` WHERE credit_code = '{sql}'", connection))
                {
                    using (MySqlDataAdapter cmd = new MySqlDataAdapter(command))
                    {
                        DataTable dt = new DataTable();
                        cmd.Fill(dt);
                        if (dt.Rows.Count > 0) return dt;
                    }
                }
                using (MySqlCommand command = new MySqlCommand($"SELECT * FROM `outer_subcontractor` WHERE credit_code = '{sql}'", connection))
                {
                    using (MySqlDataAdapter cmd = new MySqlDataAdapter(command))
                    {
                        DataTable dt = new DataTable();
                        cmd.Fill(dt);
                        if (dt.Rows.Count > 0) return dt;
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return null;
        }
        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable Where(string sql,out string excption)
        {
            excption = "";
            try
            {
                using (MySqlCommand command = new MySqlCommand($"SELECT * FROM `outer_supplier` WHERE credit_code = '{sql}'", connection))
                {
                    using (MySqlDataAdapter cmd = new MySqlDataAdapter(command))
                    {
                        DataTable dt = new DataTable();
                        cmd.Fill(dt);
                       if(dt.Rows.Count > 0) return dt;
                    }
                }
                using (MySqlCommand command = new MySqlCommand($"SELECT * FROM `outer_subcontractor` WHERE credit_code = '{sql}'", connection))
                {
                    using (MySqlDataAdapter cmd = new MySqlDataAdapter(command))
                    {
                        DataTable dt = new DataTable();
                        cmd.Fill(dt);
                        if (dt.Rows.Count > 0) return dt;
                    }
                }
            }
            catch (Exception ex)
            {
                excption = ex.Message;
            }
            return null;
        }
        /// <summary>
        /// 查询返回实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> Where<T>(string sql,out string exception) where T : class, new()
        {
            exception = "";
            try
            {
                using (MySqlDataAdapter cmd = new MySqlDataAdapter(sql, connection))
                {
                    DataTable dt = new DataTable();
                    cmd.Fill(dt);
                    return ListData<T>(dt);
                }
            }
            catch(Exception ex)
            {
                exception=ex.Message;
            }
            return null;
        }
        /// <summary>
        /// 查询返回实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> Where<T>(string sql) where T : class, new()
        {
                using (MySqlDataAdapter cmd = new MySqlDataAdapter(sql, connection))
                {
                    DataTable dt = new DataTable();
                    cmd.Fill(dt);
                    return ListData<T>(dt);
                }
        }
        /// <summary>
        /// DataTable转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private List<T> ListData<T>(DataTable dataTable) where T : class, new()
        {
            var list = new List<T>();
            var ps = typeof(T).GetProperties();

            //遍历所有DataTable的行
            foreach (DataRow dr in dataTable.Rows)
            {
                var t = new T();
                //通过反射获取T类型的所有成员
                foreach (PropertyInfo pi in ps)
                {
                    if (dataTable.Columns.Contains(pi.Name) && dr[pi.Name] != DBNull.Value)
                    {

                        object value = Convert.ChangeType(dr[pi.Name], pi.PropertyType, null);
                        pi.SetValue(t, value, null);
                    }
                }
                //将T类型添加到集合list
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// DataTable转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private List<T> ListData<T>(DataTable dataTable, out string exception) where T : class, new()
        {
            var list = new List<T>();
            var ps = typeof(T).GetProperties();
            exception = null;
            //遍历所有DataTable的行
            try
            {
                foreach (DataRow dr in dataTable.Rows)
                {
                    var t = new T();
                    //通过反射获取T类型的所有成员
                    foreach (PropertyInfo pi in ps)
                    {
                        if (dataTable.Columns.Contains(pi.Name) && dr[pi.Name] != DBNull.Value)
                        {
                            object value = Convert.ChangeType(dr[pi.Name], pi.PropertyType, null);
                            //给T类型字段赋值
                            pi.SetValue(t, value, null);
                        }
                    }
                    //将T类型添加到集合list
                    list.Add(t);
                }
            }
            catch (Exception ex)
            {
                exception = ex.Message;
            }
            return list;
        }
    }
}
